package com.itheima.dao;

import com.itheima.domain.Emp;
import com.itheima.util.JdbcUtil;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class EmpDao {
    private List<Emp> list;
    private Emp e;
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet res;

    public int getTotal() {
        int count = 0;

        list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConn();
            String sql = "select * from EMP";
            pst = conn.prepareStatement(sql);
            res = pst.executeQuery();

            while (res.next()) {
                count++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pst, res);
        }
        return count;
    }

    public List<Emp> list(int page) {
        list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConn();
            String sql = "select * from EMP limit ?,?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, (page - 1) * 3);
            pst.setInt(2, 3);
            res = pst.executeQuery();

            while (res.next()) {
                e = new Emp();
                e.setEmpno(res.getInt("empno"));
                e.setJob(res.getString("job"));
                e.setName(res.getString("ename"));
                e.setHireDate(res.getDate("hiredate").toString());
                e.setSal(res.getDouble("sal"));
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pst, res);
        }
        return list;
    }

    //根据编号删除
    public void delete(String empno) {
        try {
            conn = JdbcUtil.getConn();
            String sql = "delete from EMP where empno = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, Integer.parseInt(empno));
            pst.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pst, res);
        }
    }

    //根据编号查询信息
    public Emp findById(String empno) {
        try {
            conn = JdbcUtil.getConn();
            String sql = "select * from EMP where empno = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, Integer.parseInt(empno));
            ResultSet res = pst.executeQuery();
            if (res.next()) {
                e = new Emp();
                e.setEmpno(res.getInt("empno"));
                e.setJob(res.getString("job"));
                e.setName(res.getString("ename"));
                e.setHireDate(res.getDate("hiredate").toString());
                e.setSal(res.getDouble("sal"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            JdbcUtil.close(conn, pst, res);
        }
        return e;
    }

    public void update(Emp e) {
        try {
            conn = JdbcUtil.getConn();
            pst = conn.prepareStatement("update EMP set job=?,ename=?,hiredate=?,sal=? where empno=?");
            pst.setString(1, e.getJob());
            pst.setString(2, e.getName());
            //util.Date转sql.Date
            //new java.sql.Date(new java.util.Date().getTime())
            //new java.sql.Date(long date)   long date: date毫秒值
            pst.setDate(3, (new java.sql.Date( new SimpleDateFormat("yyyy-MM-dd").parse(e.getHireDate()).getTime())));
            pst.setDouble(4, e.getSal());
            pst.setInt(5, e.getEmpno());
            pst.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ParseException ex) {
            System.out.println(ex);;
        } finally {
            JdbcUtil.close(conn, pst, res);
        }
    }
}
